数据库sql语句及使用示例

您所在的位置：网站首页 › c 开头的国家 › 数据库sql语句及使用示例

数据库sql语句及使用示例

2024-01-25 19:59| 来源: 网络整理| 查看: 265

利用 Transact-SQL 语言创建满足以下要求的数据库：

1）数据库存在于连接 MySQL 中； 2）数据库名称为 mydb； 3）字符集选择 utf8 -- UTF-8 Unicode； 4）排序规则选择 utf8_general_ci；

具体步骤如下：点击“新建查询”，在查询编辑器输入以下代码，点击“运行”，完成后，左侧列表中选中连接“数 MySQL”，点击“刷新”按钮，列表即可见到“testdb”。

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

collate在sql中是用来定义排序规则的。排序规则其实就是当比较两个字符串时，根据某种规则来确定哪个比较大，是否相等。各个数据库支持不同的排序规则。

utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。

utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。

利用 Transact-SQL 语言查看数据库及表的信息

1.查看连接中的所有数据库步骤：点击“新建查询”，输入

SHOW DATABASES

2.查看数据库 mysql 中所有的表步骤：点击“新建查询”，输入

USE mysql; SHOW TABLES;

3.查看数据库“mysql”中表“help_keyword”的结构。步骤：点击“新建查询”，输入

USE mysql; DESC help_keyword; 或者DESCRIBE help_keyword; 或者SHOW COLUMNS FROM help_keyword;

4.查看数据库表中的内容

USE mysql; SELECT * FROM help_keyword; 利用 Transact-SQL 语言修改数据库 mydb 的字符集

步骤：点击“新建查询”，输入以下代码，点击运行

ALTER DATABASE mydb DEFAULT CHARACTER SET = latin1; 利用 Transact-SQL 语言删除数据库 testdb DROP DATABASE testdb 在数据库 xsgl 中，利用 Transact-SQL 语言创建表格：

1）表格名为 kc（课程情况表）； 2）表格中各个属性用sql描述

USE xsgl; create table kc ( kch char(4) not null, (不能取空值) kcm char(20) null, xss int null, xf int null, primary key(kch) （设置为主键） )engine=innodb default auto_increment=1;（使用innodb引擎，数据库默认编码为utf-8，自增键的起始序号为1）利用 Transact-SQL 语言修改 kc 表

1.增加“成绩”一列 cj， int 型，允许为空值，默认为 0。

ALTER TABLE kc ADD COLUMN cj INT DEFAULT 0;

2.修改cj列的类型为char。

ALTER TABLE kc CHANGE COLUMN cj cj CHAR(4);

3.修改cj列的列名为mark。

ALTER TABLE kc CHANGE cj mark CHAR(4) DEFAULT 0;

4.删除mark列。

ALTER TABLE kc DROP COLUMN mark;

5.删除表 kc。

DROP TABLE kc

6.利用 Transact-SQL 将表 xs 重命名为 Students。

RENAME TABLE xsgl.xs TO xsgl.Students;

7.使用alert table增加和删除列和改变数据类型

ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name ; DROP COLUMN column_name; ALTER TABLE table_name ALTER COLUMN column_name datatype 简单查询语句

1.查询 world 数据库的 country 表中的国名（Name）、洲名（Continent）和地区（Region）。

USE world SELECT NAME,Continent,Region FROM country;

2.从 world 数据库的 city 表中搜索返回前 20 条的数据。

USE world; SELECT * FROM city LIMIT 0,20;

其中，“LIMIT M,N”中的 M 表示从第 M 条（不包括 M）开始

3.使用 WHERE 子句从 world 数据库的 country 表中检索出所有领土面积超过一百万平方公里的国家名称（Name）、洲名（Continent）以及领土面积（SurfaceArea）

USE world; SELECT NAME,Continent,SurfaceArea FROM country WHERE SurfaceArea > 1000000;

4.查询在 world 数据库的 country 表中 Name 以字母 C 开头的国家的洲名 Continent,地区 Region

USE world; SELECT Name,Continent,Region FROM country WHERE NAME like 'C%';

5.查询 world 数据库 Country 表中所有国家的 Name 和 Condinent，并按生日 SurfaceArea 从小到大进行排列。

USE world; SELECT Name,Continent,SurfaceArea FROM country ORDER BY SurfaceArea;

如果是降序排列的话

ORDER BY Population DESC;

6.country表中查询独立年份为空的国家的名称、国家年份。

USE world; SELECT NAME,IndepYear FROM country WHERE IndepYear is null; 使用 Transact-SQL 语言输入表数据、修改表数据和删除表数据

1.在表 newlanguage 中插入中国的客家话，其名称为 CountryCode 为 CHN， language 为 Kejia， isOfficial 为 F， percentage 为 0.3。

USE world; INSERT INTO newlanguage VALUES ('CHN','Kejia','F',0.3);

2.试将表 countrylanguage 中的所有记录插入到表 newlanguage 中去，

USE world; INSERT INTO newlanguage SELECT * FROM countrylanguage;

3.将表 newlanguage 中 Language 为“Kejia” 的语言的 Percentage 改为 0.4

USE world; UPDATE newlanguage SET Percentage = 0.4 WHERE Language = 'Kejia';

4.将表 newlanguage 中语言的 Percentage 均减去 0.1

USE world; UPDATE newlanguage SET Percentage = Percentage - 0.1;

5.删除表 newlanguage 中澳大利亚（CountrCode 为“AUS”）的英语记录。

USE world; DELETE FROM newlanguage WHERE CountryCode='AUS' AND Language='English';

6.清空表 newlanguage 中的所有数据。

USE world; TRUNCATE TABLE newlanguage; 视图管理

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *，而你看到的将是你在视图定义里给出的那些数据列

1.建立亚洲国家的视图 asia_country，并要求进行修改和插入操作时仍需保证该视图只有亚洲国家，视图的属性名为 Code， Name， Continent， Population， GNP, GovernmentForm.

USE world; CREATE VIEW asia_country AS SELECT Code,Name,Continent,Population,GNP,GovernmentForm FROM country WHERE Continent='Asia';

2.建立国家简称（country.Code）、城市名(city.Name)、语言名(countrylanguage.Language) 的视图 cc_language。本视图由三个基本表的连接操作导出

USE world; CREATE VIEW cc_language AS SELECT country.Code,city.Name,countrylanguage.Language FROM country,city,countrylanguage WHERE country.Code = city.CountryCode AND country.Code = countrylanguage.CountryCode; 数据类型

1.日期和时间数据类型

数据类型含义 date 3字节，日期，格式：2014-09-18 time 3字节，时间，格式，09:20:20 datetime 8字节，日期格式，格式：2014-09-18 08:42:40 timestamp 4字节，自动存储记录修改时间 year 1字节，年份

2.整型

mysql数据类型含义（有符号） tinyint 1字节，范围（-128~127) smallint 2字节，范围（-32768~32767） int 4字节（-2147483648~-2147483648)

3.浮点型

mysql数据类型含义 float(m,d) 4字节，单精度浮点，m总个数，d小数位 double(m,d) 8字节，双精度浮点，m总个数，d小数位 decimal(m,d) decimal是存储为字符串的浮点数

4.字符串类型

mysql数据类型含义 char(n) 固定长度，最多255个字符 varchar(n) 可变长度，最多65535个字符 tinytext 可变长度，最多255个字符 text 可变长度，最多65535个字符

char（n）和 varchar（n）中括号中 n 代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入 m 个中文，但是实际会占用 m*3 个字节

同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间，而 varchar 只会占用实际字符应该占用的空间+1，并且实际空间+1 10000000;

3.统计 country 表中共和国政体的国家数。

USE world; SELECT COUNT(Code)//计算数量，计算code数量 FROM country WHERE GovernmentForm = 'Republic';

4.统计 country 表中共和国政体国家的平均人口。

USE world; SELECT AVG(Population)//计算平均 FROM country WHERE GovernmentForm = 'Republic';

5.分组统计 country 表中各政体的国家个数。

USE world; SELECT GovernmentForm,count(*)//计算 FROM country GROUP BY GovernmentForm;//分组

6.查询有超过两条城市记录的国家的名称。

USE world; SELECT country.Name FROM city,country WHERE city.CountryCode = country.Code GROUP BY city.CountryCode HAVING COUNT(city.Name) > 2; //表示拥有的数量

7、查询非共和政体的国家的名称和政体

USE world; SELECT Name,GovernmentForm FROM country WHERE GovernmentForm 'Republic'; //怎样取非

8.查询平均每国人口数高于非洲的大洲名称及该平均数，以平均数的降序排列

USE world; SELECT Continent,AVG(Population) FROM country GROUP BY Continent HAVING AVG(Population) > (SELECT AVG(Population) FROM country WHERE Continent = 'Africa') ORDER BY AVG(Population) DESC;//嵌套循环

9.查询 city 表中多于 3 个城市记录且缩写以“A”开头的国家的名称和城市平均人口，以平均人口的升序排列

USE world; SELECT city.CountryCode,country.Name,AVG(city.Population) FROM city,country WHERE city.CountryCode LIKE 'A%' AND city.CountryCode = country.Code GROUP BY city.CountryCode HAVING COUNT(city.Name) > 3 ORDER BY AVG(city.Population);

10.查找零件表中最小

USE test; select pno from p having min(weight);

11.查找同时为J1和J2提供零件的供应商代码

USE test; select sno from spj where jno='J1' and sno in (select sno from spj where jno='j2') 使用sql语言创建复杂数据库

1.学生基本表和课程基本表

要求：

1、在 xs 表中定义 xh 为主键。 2、在 kc 表中定义 xh 和 kch 联合构成主键。 3、定义 kc 表中的 kcm 列满足唯一性约束。 4、定义 kc 表中的 fs 列默认值为 0。 5、定义 xs 表中的 xb 列的 CHECK 约束“男”或“女”。 6、在 xs 表与 kc 表之间定义外键 xh。

xs表：

use xsgl; create table xs ( xh int not null, xm char(8) null, xb char(2) null check(xb in('男','女')),//check约束，范围规定，枚举值规定，特定匹配 nl tinyint null, zy char(16) null, jtzz char(50) null, primary key(xh) )engine=innodb default charset=utf8 auto_increment=1;

kc表

create table kc ( xh int not null, kch int not null, kcm char(20) null unique, xss int null, xf int null, fs int null default 0, primary key(xh,kch), foreign key(xh) references xs(xh) on delete cascade on update cascade//外键,并且xs的xh发生改变时，跟随改变和删除 )engine=innodb default charset=utf8 auto_increment=1;

2.创建一个学生—课程数据库，包括学生关系Student、课程关系Course和选修关系SC

1)建立学生-课程数据库xskc，创建Student表，并将Student表中的Sno属性定义为主键

2)创建Course表和SC表，并将SC表中的Sno,Cno联合构成主键

CREATE DATABASE xskc; USE xskc; CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ) USE xskc; CREATE TABLE Course (Cno CHAR(9) PRIMARY KEY, Cname CHAR(20), Cpno CHAR(4), Ccredit SMALLINT ) ; CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(9) NOT NULL, Grade SMALLINT, PRIMARY KEY(Sno,Cno) )

3）定义sc表中参照完整性

USE xskc; ALTER TABLE SC ADD FOREIGN KEY (Sno) REFERENCES Student(Sno); ALTER TABLE SC ADD FOREIGN KEY (Cno) REFERENCES Course(Cno);

我们常常希望保证在一个关系中给定属性集上的取值也在另一个关系的特定属性集的取值中出现。这种情况称为参照完整性(referential integrity)

外键级联的三种方式

1）：on delete no action 数据库默认的方式，禁止删除，就是不级联删除

2）：on delete cascade 级联删除

3）：on delete set null 不级联删除，将外键级联的字段赋个空值。

数据库自定义函数

需要mysql实现sql逻辑处理，参数是IN参数，含有returns语句用来指定函数返回类型

创建：CREATE FUNCTION函数名称(参数列表)

RETURNS 返回值类型

函数体

修改： ALTER FUNCTION 函数名称 [characteristic ...]

删除：DROP FUNCTION [IF EXISTS] 函数名称

调用：SELECT 函数名称(参数列表)

数据库存储过程

一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高，用户通过指定存储过程的名字并给出参数来执行它。参数可以为IN, OUT, 或INOUT

创建：CREATE PROCEDURE 过程名 (参数列表) [characteristic ...]

函数体

修改： ALTER PROCEDURE 过程名 [characteristic ...]

删除：DROP PROCEDURE [IF EXISTS] 过程名

调用：CALL 过程名(参数列表)

存储过程与函数区别

1.存储过程，功能强大，可以执行包括修改表等一系列数据库操作，也可以创建为数据库启动时自动运行的存储过程。

自定义函数，用户定义函数不能用于执行一组修改全局数据库状态的操作。 2.对于sql server: 存储过程，可以使用非确定函数。自定义函数，不允许在用户定义函数主体中内置非确定函数。

3.存储过程，可返回记录集。

自定义函数，可以返回表变量。 4.存储过程，其返回值不能被直接引用。

自定义函数，其返回值可以被直接引用。 5.存储过程，用 CALL 语句执行。

自定义函数，在查询语句中调用。

触发器

与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

创建：

CREATE TRIGGER --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. { BEFORE | AFTER } --触发器有执行的时间设置：可以设置为事件发生前或后。 { INSERT | UPDATE | DELETE } --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。 ON --触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。 FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。 --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句，包括复合语句，但是这里的语句受的限制和函数的一样。 --创建触发器（CREATE TRIGGER），需要SUPER权限。

eg:

CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END

可以用来进行另一种级联式修改

删除：DROP TRIGGER 方案名称.触发器名称

事件

可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似于linux系统下面的任务调度器crontab，或者类似与window下面的计划任务。值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

创建

CREATE [DEFINER = { user | CURRENT_USER }] --定义事件执行的时候检查权限的用户。 EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule --定义执行的时间和时间间隔。 [ON COMPLETION [NOT] PRESERVE] --定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。 [ENABLE | DISABLE | DISABLE ON SLAVE] --定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE [COMMENT 'comment'] -- 注释 DO event_body; schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval]...] [ENDS timestamp [+ INTERVAL interval] ...] interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR| MINUTE | WEEK | SECOND | YEAR_MONTH |DAY_HOUR | DAY_MINUTE |DAY_SECOND| HOUR_MINUTE | HOUR_SECOND| MINUTE_SECOND}

删除：

DROP EVENT [IF EXISTS] event_name

修改：

ALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] [DO event_body]

查看是否开启了事件

SHOW VARIABLES LIKE 'event_scheduler'; SELECT @@event_scheduler; SHOW PROCESSLIST;

如果看到event_scheduler为on或者PROCESSLIST中显示有event_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event_scheduler的信息，那么就说明事件没有开启，我们需要开启它。

【本文地址】

数据库sql语句及使用示例

数据库sql语句及使用示例

今日新闻

推荐新闻